package com.hk.commons;


import com.hk.commons.entity.sys.SysUser;
import com.hk.commons.entity.sys.UserChangeInfo;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;


public class ReadExcelToUserChg {

    private static Logger logger = LoggerFactory.getLogger(ReadExcelToUserChg.class);
    /**
     * read the Excel file
     * @param path the path of the Excel file
     * @return
     * @throws IOException
     */
	
	
	
    public static List<UserChangeInfo> readExcel(String path) throws IOException {
        if (StringHelper.isEmpty(path)) {
            return null;
        } else {
            String postfix = getPostfix(path);
            if (StringHelper.isNotEmpty(postfix)) {
                if (Constant.OFFICE_EXCEL_2003_POSTFIX.equals(postfix)) {
                    return readXls(path);
                } else if (Constant.OFFICE_EXCEL_2010_POSTFIX.equals(postfix)) {
                    return readXlsx(path);
                }
            } else {
                logger.info("file is not excel file  path={}, postfix={}", path, postfix);
            }
        }
        return null;
    }

    /**
     * Read the Excel 2010
     * @param path the path of the excel file
     * @return
     * @throws IOException
     */
    public static List<UserChangeInfo> readXlsx(String path) throws IOException {
        logger.info(Constant.PROCESSING + path);
        InputStream is = new FileInputStream(path);
         
		XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);

        List<UserChangeInfo> list = new ArrayList<UserChangeInfo>();
        // Read the Sheet
        for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
            XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
            if (xssfSheet == null) {
                continue;
            }
            // Read the Row
            for (int rowNum = 1; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
                XSSFRow xssfRow = xssfSheet.getRow(rowNum);
                if (xssfRow != null) {
                    UserChangeInfo userChg = new UserChangeInfo();

                    XSSFCell  userPhone = xssfRow.getCell((short) 0);
                    XSSFCell  realName = xssfRow.getCell((short) 1);
                    XSSFCell  oldOrgId = xssfRow.getCell((short) 2);
                    XSSFCell oldOrgFullName = xssfRow.getCell((short) 3);
                    XSSFCell oldChannelName = xssfRow.getCell((short) 4);
                    XSSFCell newChannelName = xssfRow.getCell((short) 7);
                    XSSFCell newDepName01 = xssfRow.getCell((short) 8);
                    XSSFCell newDepName02 = xssfRow.getCell((short) 9);
                    XSSFCell newDepName03 = xssfRow.getCell((short) 10);
                    XSSFCell newDepName04 = xssfRow.getCell((short) 11);
                    XSSFCell newDepName05 = xssfRow.getCell((short) 12);
                    XSSFCell newDepName06 = xssfRow.getCell((short) 13);

                    String phone = getValue(userPhone);
                    userChg.setName(phone);

                    String realNameString = getValue(realName);
                    userChg.setRealName(realNameString);

                    if(oldOrgId != null){
                        String orgIdStr = getValue(oldOrgId);
                        if(StringHelper.isNotEmpty(orgIdStr)){
                            userChg.setOldOrgId(Integer.valueOf(orgIdStr));
                        }
                    }

                    String oldOrgFullNameStr = getValue(oldOrgFullName);
                    if(oldOrgFullNameStr != null){
                        userChg.setOldOrgFullName(oldOrgFullNameStr);
                    }

                    String oldChannelNameStr = getValue(oldChannelName);
                    if(oldChannelNameStr != null){
                        userChg.setOldChannelName(oldChannelNameStr);
                    }

                    String newChannelNameStr = getValue(newChannelName);
                    if(newChannelNameStr != null){
                        userChg.setNewChannelName(newChannelNameStr);

                        if(newChannelNameStr.trim().equals("多元渠道部")){
                            userChg.setNewOrgId(1076);
                        }else	if(newChannelNameStr.trim().equals("内部渠道部")){
                            userChg.setNewOrgId(1075);
                        }else	if(newChannelNameStr.trim().equals("直销渠道二部")){
                            userChg.setNewOrgId(1026);
                        }else	if(newChannelNameStr.trim().equals("网络渠道部")){
                            userChg.setNewOrgId(1017);
                        }else	if(newChannelNameStr.trim().equals("直销渠道一部")) {
                            userChg.setNewOrgId(3);
                        }
                    }

                    String newDepName01Str = getValue(newDepName01);
                    if(newDepName01Str != null){
                        userChg.setNewDepName01(newDepName01Str);
                    }

                    String newDepName02Str = getValue(newDepName02);
                    if(newDepName02Str != null){
                        userChg.setNewDepName02(newDepName02Str);
                    }


                    String newDepName03Str = getValue(newDepName03);
                    if(newDepName03Str != null){
                        userChg.setNewDepName03(newDepName03Str);
                    }

                    String newDepName04Str = getValue(newDepName04);
                    if(newDepName04Str != null){
                        userChg.setNewDepName04(newDepName04Str);
                    }

                    String newDepName05Str = getValue(newDepName05);
                    if(newDepName05Str != null){
                        userChg.setNewDepName05(newDepName05Str);
                    }

                    String newDepName06Str = getValue(newDepName06);
                    if(newDepName06Str != null){
                        userChg.setNewDepName06(newDepName06Str);
                    }

                    logger.info("u serChg ={}", userChg);

                    list.add(userChg);
                }
            }
        }
        return list;
    }

    /**
     * Read the Excel 2003-2007
     * @param path the path of the Excel
     * @return
     * @throws IOException
     */
    public static List<UserChangeInfo> readXls(String path) throws IOException {
        logger.info(Constant.PROCESSING + path);
        InputStream is = new FileInputStream(path);
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
        UserChangeInfo userChg = null;
        List<UserChangeInfo> list = new ArrayList<UserChangeInfo>();
        // Read the Sheet

        for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
            HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
            if (hssfSheet == null) {
                continue;
            }
            // Read the Row
            for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
                HSSFRow hssfRow = hssfSheet.getRow(rowNum);
                if (hssfRow != null) {
                    userChg = new UserChangeInfo();
                    HSSFCell  userPhone = hssfRow.getCell((short) 0);
                    HSSFCell  realName = hssfRow.getCell((short) 1);
                    HSSFCell  oldOrgId = hssfRow.getCell((short) 2);
                    HSSFCell oldOrgFullName = hssfRow.getCell((short) 3);
                    HSSFCell oldChannelName = hssfRow.getCell((short) 4);
                    HSSFCell newChannelName = hssfRow.getCell((short) 7);
                    HSSFCell newDepName01 = hssfRow.getCell((short) 8);
                    HSSFCell newDepName02 = hssfRow.getCell((short) 9);
                    HSSFCell newDepName03 = hssfRow.getCell((short) 10);
                    HSSFCell newDepName04 = hssfRow.getCell((short) 11);
                    HSSFCell newDepName05 = hssfRow.getCell((short) 12);
                    HSSFCell newDepName06 = hssfRow.getCell((short) 13);


                    String phone = getValue(userPhone);
                    userChg.setName(phone);

                    String realNameString = getValue(realName);
                    userChg.setRealName(realNameString);

                    if(oldOrgId != null){
                        String orgIdStr = getValue(oldOrgId);

                        if(StringHelper.isNotEmpty(orgIdStr)){
                            userChg.setOldOrgId(Integer.valueOf(orgIdStr));
                        }
                    }

                    String oldOrgFullNameStr = getValue(oldOrgFullName);
                    if(oldOrgFullNameStr != null){
                        userChg.setOldOrgFullName(oldOrgFullNameStr);
                    }

                    String oldChannelNameStr = getValue(oldChannelName);
                    if(oldChannelNameStr != null){
                        userChg.setOldChannelName(oldChannelNameStr);
                    }



                    String newChannelNameStr = getValue(newChannelName);
                    if(newChannelNameStr != null){
                        userChg.setNewChannelName(newChannelNameStr);
                    }

                    String newDepName01Str = getValue(newDepName01);
                    if(newDepName01Str != null){
                        userChg.setNewDepName01(newDepName01Str);
                    }

                    String newDepName02Str = getValue(newDepName02);
                    if(newDepName02Str != null){
                        userChg.setNewDepName02(newDepName02Str);
                    }


                    String newDepName03Str = getValue(newDepName03);
                    if(newDepName03Str != null){
                        userChg.setNewDepName03(newDepName03Str);
                    }

                    String newDepName04Str = getValue(newDepName04);
                    if(newDepName04Str != null){
                        userChg.setNewDepName04(newDepName04Str);
                    }

                    String newDepName05Str = getValue(newDepName05);
                    if(newDepName05Str != null){
                        userChg.setNewDepName05(newDepName05Str);
                    }

                    String newDepName06Str = getValue(newDepName06);
                    if(newDepName06Str != null){
                        userChg.setNewDepName06(newDepName06Str);
                    }

                    //logger.info("userChg ={}", userChg);
                    list.add(userChg);
                }
            }
        }
        return list;
    }

     
    private static String getValue(XSSFCell xssfRow) {

        return String.valueOf(xssfRow.getStringCellValue()).trim();
    }

    
    private static String getValue(HSSFCell hssfCell){
        if(hssfCell == null){
            return "";
        }

        //logger.info("hssfCell getCellType ={}", hssfCell.getCellType());
    	if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {
            //logger.info("hssfCell CELL_TYPE_BOOLEAN  value ={}", String.valueOf(hssfCell.getBooleanCellValue()));
             // 返回布尔类型的值
             return String.valueOf(hssfCell.getBooleanCellValue());
        } else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {
             // 返回数值类型的值
            //logger.info("hssfCell CELL_TYPE_NUMERIC  value ={}", String.valueOf(hssfCell.getNumericCellValue()));

             return String.valueOf(hssfCell.getNumericCellValue());
       } else {
            // 返回字符串类型的值
            //logger.info("hssfCell getRichStringCellValue  value ={}", String.valueOf(hssfCell.getRichStringCellValue()));
            return String.valueOf(hssfCell.getRichStringCellValue());
       }
    }
    
    
    public static String getPostfix(String path) {
    	if (StringHelper.isEmpty(path)) {
    	         return null;
    	}
    	 
    	if (path.contains(Constant.POINT)) {
    	         return path.substring(path.lastIndexOf(Constant.POINT) + 1, path.length());
    	}
    	return null;
    }
}
